<?php
class MysqlBase{
	const RESULT_FIELD_NAME_LOWER = false; 
	
	var $conn = false;
	
	function __construct($dbHost,$dbUser,$dbPassword,$dbName,$charset='UTF8'){
		$this->conn = mysqli_connect ( $dbHost,$dbUser,$dbPassword, $dbName );
        
        if (! $this->conn){
           JYTools::error('数据库存连接失败！');	
           return;
        }
        mysqli_query( $this->conn, "SET NAMES '".$charset."'" );
        mysqli_autocommit( $this->conn, true);
	}

	function execute($sql){
		$stmt = &$this->_execute($sql);		
	
		$rs = $stmt? mysqli_affected_rows($this->conn):-1;
		if($stmt instanceof mysqli_result) mysqli_free_result($stmt);
		return $rs;
	}

	function query($sql, $page = 0, $pageSize = 10,&$totalCount=0){
		//不分页
		if($page==0){
			$stmt = &$this->_execute($sql);
			$data = array();
			while($row=$this->fetch($stmt)){
				$data[]= $row;
			}
			$totalCount = count($row);
			if($stmt instanceof mysqli_result) mysqli_free_result($stmt);
			return $data;
		}
	
		$sqlCount = "select count(1) cnt  from (".$this->stripOrderBy($sql).") t";
		$totalCount = $this->query($sqlCount);
		$limit = array($pageSize,($page-1)*$pageSize);
		$totalCount = $totalCount[0]['cnt'];
		$sql .= ' limit '.(($page-1)*$pageSize).','.$pageSize;
		
		return $this->query($sql);
	}

	function find($table,$where,$fields='*',$order=''){

		$row = $this->findAll($table,$where,$fields,$order,1,1);
		return $row[0];
	}

	function findAll($table,$where='',$fields='*',$order='', $page = 0, $pageSize = 10,&$totalCount=0){
		$sql = "select {$fields}  from ".$table." where 1=1 "
		.($where?" and ".$where:"")
		.($order?" order by ".$order:"");
		
		return $this->query($sql,$page, $pageSize,&$totalCount);
	}

	function update($table,$where,$row){
	 	$string = $this->compile_update_string ( $row );
        $sql = 'UPDATE ' . $table . ' SET ' . $string.' WHERE ' . $where;
 
        return $this->execute($sql);
	}

	function create($table,$row,$pkField=''){
		
		$formated_array = $this->compile_insert_string($row);

        $sql = 'INSERT INTO ' . $table . ' (' . $formated_array ['FIELD_NAMES'] . ') VALUES(' . $formated_array ['FIELD_VALUES'] . ')';
       	
		$stmt = &$this->_execute($sql);
		//当传入主键时，成功默认返回1
		$nowid = $pkField?mysqli_insert_id($this->conn):1;
		$nowid = $stmt?$nowid:-1;		
		if($stmt instanceof mysqli_result) mysqli_free_result($stmt);
		return $nowid;
	}
	
	function del($table,$where){
		if(is_array($where)){
			$where = $this->compile_update_string ( $where );
		}
        $sql = "DELETE FROM ".$table." WHERE ".$where;
 
        return $this->execute($sql);
	}
	
	/*执行SQL
	参数:
	@params $sql string
	@params $row array 
	*/
	private function &_execute($sql){
		if(empty($this->conn)){
           JYTools::error("Not connect database");
        }
        
		JYTools::log($sql);
        $result = mysqli_query ($this->conn, $sql);

        if($result === false){
			JYTools::error($sql.": Query String error." . mysqli_error($this->conn));
        }
        
       	return $result;
	}

	/*取下一行数据
	返回:成功，返回结果数组
	*/
	private function fetch(&$stmt){
		$row = @mysqli_fetch_array($stmt, MYSQLI_ASSOC);
        if ($row && MysqlBase::RESULT_FIELD_NAME_LOWER) {
            $row = array_change_key_case($row, CASE_LOWER);
        }
		return $row;
	}
	
 	/**
     * format update set
     *
     * @param array $data
     * @return formated $data
     */
    private function compile_update_string($data){
        $return = '';
        foreach( $data as $k => $v ){
            if (is_string($v)||!$v){
                $return .= "`$k`='" . $this->formatString ( $v ) . "',";
            }else{
                $return .= "`$k`=" . $v . ",";
            }
        }
        $return = preg_replace ( "/,$/", "", $return );

        return $return;
    }
    
 /**
     * format  insert set
     *
     * @param array $data
     * @return formated $data
     */
    private function compile_insert_string($data){
        $field_names = '';
        $field_values = '';

        foreach ( $data as $k => $v ){
            $field_names .= "`{$k}`,";

            if (is_string($v)||!$v){
                $field_values .= "'" . $this->formatString ( $v ) . "',";
            }else{
                $field_values .= $v . ",";
            }
        }

        $field_names = preg_replace ( "/,$/", "", $field_names );
        $field_values = preg_replace ( "/,$/", "", $field_values );

        return array ('FIELD_NAMES' => $field_names, 'FIELD_VALUES' => $field_values );
    }

  /**
     * Escapes special characters in a string for use in a SQL statement
     *
     * @param string $str    the query sql string
     * @return string $str   the formatted string
     */
    public function formatString($str){
        if (get_magic_quotes_gpc ()){
            $str = stripslashes ( $str );
        }
        if (! is_numeric ( $str )){
            $str = mysqli_real_escape_string ( $this->conn, $str );
        }
        return $str;
    }
    
	private function stripOrderBy($sql){
		$rez = preg_match('/(\sORDER\s+BY\s[^)]*)/is',$sql,$arr);
		if ($arr)
			if (strpos($arr[0],'(') !== false) {
				$at = strpos($sql,$arr[0]);
				$cntin = 0;
				for ($i=$at, $max=strlen($sql); $i < $max; $i++) {
					$ch = $sql[$i];
					if ($ch == '(') {
						$cntin += 1;
					} elseif($ch == ')') {
						$cntin -= 1;
						if ($cntin < 0) {
							break;
						}
					}
				}
				$sql = substr($sql,0,$at).substr($sql,$i);
			} else
				$sql = str_replace($arr[0], '', $sql); 
		return $sql;
	 }    

	function close(){
	    if($this->conn != null){
            mysqli_close ( $this->conn );
            $this->conn = null;
        }
	}

	function __destruct(){
		$this->close();
	} 
}